﻿Imports Microsoft.VisualBasic
Imports Logging

Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System
Imports System.Configuration

Public Class DB

    Private con As SqlConnection
    Private cmd As New SqlCommand
    Private dataAdapter As SqlDataAdapter
    Private dataSet As DataSet

    Private logger As Logger = New Logger("C:\\netwerkscanner.log")

    Public ReadOnly Property ConnectionString() As String
        Get
            Try
                Return ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
            Catch ex As Exception
                logger.WriteLine("db", "Connectie string is niet geconfigureerd: " + ex.Message)
                Return ""
            End Try
        End Get
    End Property

    Public Sub New()
        Try
            con = New SqlConnection(ConnectionString)
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            con.Open()
        Catch ex As Exception
            logger.WriteLine("db", "Fout bij verbinden met de databank. Foute connectie string: " + ConnectionString)
            logger.WriteLine("db", ex.Message)
            Throw ex
        End Try
    End Sub

#Region "Add stuff"
    Public Sub AddUser(ByVal ssid As String, ByVal ip As String, ByVal mac As String, ByVal datum As String)
        With cmd.Parameters
            .AddWithValue("SSID", ssid)
            .AddWithValue("IP", ip)
            .AddWithValue("MAC", mac)
            .AddWithValue("datum", datum)
        End With
        ExecuteStoredProcedureNonQuery("AddHosts")
    End Sub

    Public Sub AddAccesPoints(ByVal ssid As String, ByVal mac As String)
        With cmd.Parameters
            .AddWithValue("SSID", ssid)
            .AddWithValue("MAC", mac)
        End With
        ExecuteStoredProcedureNonQuery("AddAccesPoints")
    End Sub
#End Region

#Region "Stored procedure executie methoden"

    Private Function ExecuteStoredProcedureScalar(ByVal storedProc As String) As Object
        Using con
            Try
                cmd.CommandText = storedProc
                Return cmd.ExecuteScalar
            Catch ex As Exception
                logger.WriteLine("db", "Fout bij uitvoeren stored procedure (scalar)" + storedProc + ": " + ex.Message)
                Throw ex
            End Try
        End Using
    End Function

    Private Function ExecuteStoredProcedureNonQuery(ByVal storedProc As String) As Integer
        Using con
            Try
                cmd.CommandText = storedProc
                Return cmd.ExecuteNonQuery
            Catch ex As Exception
                logger.WriteLine("db", "Fout bij uitvoeren stored procedure (nonquery)" + storedProc + ": " + ex.Message)
                Throw ex
            End Try
        End Using
    End Function

    Private Function ExecuteStoredProcedure(ByVal storedProc As String) As DataSet
        Using con
            Try
                cmd.CommandText = storedProc

                dataAdapter = New SqlDataAdapter(cmd)
                dataSet = New DataSet()
                dataAdapter.Fill(dataSet)

                Return dataSet
            Catch ex As Exception
                logger.WriteLine("db", "Fout bij uitvoeren stored procedure " + storedProc + ": " + ex.Message)
                Throw ex
            End Try
        End Using
    End Function

#End Region

End Class
